// This file merges data from 1997 - 2017 Ag Census on Livestock
// downloaded from Quick Stats https://quickstats.nass.usda.gov/
cd "C:\Users\16308\OneDrive - Montana State University\UW_Madison\Water quality\Replication Package\Data\NassAnimalUnits\Raw"

// Import data on cattle, chicken and hogs separately and append 

clear
gen year = .
save animal_totals_imputed, replace 

local animals cattle chickens hogs
foreach animal of local animals{
import delimited `animal'.csv, clear 
append using animal_totals_imputed, force
save animal_totals_imputed, replace 
}


// create unique county code in case useful for merging 
gen low = 1 if stateansi < 10 
gen low2 = 1 if countyansi < 10
gen low3 = 1 if countyansi < 101 & countyansi > 9
tostring stateansi, replace
tostring countyansi, replace 
replace stateansi = "0" + stateansi if low == 1 
replace countyansi = "00" + countyansi if low2 == 1
replace countyansi = "0" + countyansi if low3 == 1
gen county_code = stateansi + countyansi 
drop low low2
drop if countyansi == ""


// Conversion factors based on average AU for the category -- already averaging over types of animals in the category
// https://lacrossecounty.org/docs/default-source/land-conservation/animalunitcalculator.pdf?sfvrsn=d1881d7d_2
gen conversion = 1 if dataitem == "CATTLE, INCL CALVES - INVENTORY"
replace conversion = 0.4 if dataitem == "HOGS - INVENTORY"
replace conversion = 0.01 if dataitem == "CHICKENS, LAYERS - INVENTORY"

// some counties have too few operations to report the number of animals.  We're just going to assign a value of zero to these (should be low anyway)
drop if value == " (D)"
replace value = subinstr(value, ",", "", .)

destring value, replace 

// reweight each commodity to AU
gen animals_weighted = value * conversion 

// county x year level AU count
collapse (first) county countyansi state stateansi (sum) animals_weighted, by(county_code year)

// the name of anchorage/kenai peninsula changes.  Drop for reshape (plus we won't use it)
drop if county_code == "02."
reshape wide animals_weighted, i(county_code) j(year)

// linearly impute the value of animal units in years between census 
forval start = 1997(5)2012{
	local plus1 = `start' + 1
	local plus2 = `start' + 2
	local plus3 = `start' + 3
	local plus4 = `start' + 4
	local end = `start'+5
	gen dif = animals_weighted`end' - animals_weighted`start'
	gen animals_weighted`plus1' = animals_weighted`start' + (dif/5) * 1
	gen animals_weighted`plus2' = animals_weighted`start' + (dif/5) * 2
	gen animals_weighted`plus3' = animals_weighted`start' + (dif/5) * 3
	gen animals_weighted`plus4' = animals_weighted`start' + (dif/5) * 4
	drop dif
}

// return to long 
reshape long animals_weighted, i(county_code) j(year)

save animal_totals_imputed, replace 
